package seatechit.msbgateway.dbaccess.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import seatechit.msbgateway.dbaccess.SpringFrameworkDAO;
import seatechit.msbgateway.dbaccess.dao.AccountInfoDAO;
import seatechit.msbgateway.dbaccess.entity.AccountInfo;
import seatechit.msbgateway.utils.AppUtils;
import seatechit.msbgateway.utils.Global;

public class bk_AccountInfoDAOImpl extends SpringFrameworkDAO implements AccountInfoDAO {

	public bk_AccountInfoDAOImpl() {
	}

	@Override
	public AccountInfo getAccountInfoByAccountNo(String acct_no, String acct_type) {
		Connection connToSIBS = null;
		PreparedStatement pst1 = null;
		AccountInfo temAccountInfo = null;
		try {
			connToSIBS = getDb2CoreJDBC("10.0.1.1", "salarydele", "salarydele");
			String strSQL = "";
			if (acct_type.equals(Global.COREBBANK_CA_ACCOUNT_TYPE) || acct_type.equals(Global.COREBBANK_SA_ACCOUNT_TYPE)) {
				strSQL += "SELECT mst.bank_no,mst.branch_no,mst.acct_no,'' p_acct_no,mst.acct_type,mst.acct_name,mst.acct_ccy, ";
				strSQL += "  mst.cif_no,mst.acct_status,mst.issued_date,mst.date_acct_opened,'' maturity_date,'' establish_date, ";
				strSQL += "  '' full_release_date,COALESCE (mst.memo_bal, mst.all_bal) real_bal,'' product_code,'' term,'' term_type, ";
				strSQL += "  mst.passbook_no,mst.interest_rate,mst.accured_interest,mst.accured_interest_due,'' loan_no ";
				strSQL += "  FROM (SELECT '032' bank_no,TRIM (a.branch) branch_no,TRIM (a.acctno) acct_no,TRIM (a.actype) acct_type, ";
				strSQL += "  TRIM (a.acname) acct_name,TRIM (a.ddctyp) acct_ccy,TRIM (a.cifno) cif_no,TRIM (a.status) acct_status, ";
				strSQL += "  TRIM (a.datop6) date_acct_opened,TRIM (a.datop7) issued_date,TRIM (a.rate) interest_rate,TRIM(a.accrue) accured_interest,TRIM('') accured_interest_due,TRIM (a.odlimt) overdraft_limit, ";
				strSQL += "  TRIM (a.hold || ':' || a.cbal || ':' || (a.cbal - a.hold) || ':' || a.odlimt || ':' || a.whhirt) all_bal,(SELECT TRIM(tmbmrser) FROM stdattrn.tmpbmast WHERE tmbmacct = a.acctno) passbook_no, ";
				strSQL += "  (SELECT TRIM(sub.hold || ':' || sub.cbal || ':' || (sub.cbal - sub.hold) || ':' || sub.odlimt || ':' || a.whhirt) inner_bal ";
				strSQL += "  FROM  stdattrn.ddmemo sub WHERE  sub.acctno = '" + acct_no + "' AND a.acctno = sub.acctno AND a.cifno = sub.cifno ";
				strSQL += "  AND a.status <> '2') memo_bal FROM stdattrn.ddmast a,stdattrn.cfacct b ";
				strSQL += "  WHERE b.cfaccn = '" + acct_no + "' AND a.acctno = b.cfaccn AND a.cifno = b.cfcifn AND a.status <> '2') mst ";
				strSQL += "  UNION ALL ";
				strSQL += "SELECT mst.bank_no,mst.branch_no,mst.acct_no,'' p_acct_no,mst.acct_type,mst.acct_name,mst.acct_ccy,";
				strSQL += "  mst.cif_no,mst.acct_status,mst.issued_date,mst.date_acct_opened,'' maturity_date,'' establish_date,";
				strSQL += "  '' full_release_date,COALESCE (mst.memo_bal, mst.all_bal) real_bal,'' product_code,'' term,'' term_type,";
				strSQL += "  mst.passbook_no,mst.interest_rate,mst.accured_interest,mst.accured_interest_due,'' loan_no";
				strSQL += "  FROM (SELECT '032' bank_no,TRIM (a.branch) branch_no,TRIM (a.acctno) acct_no,TRIM (a.actype) acct_type,";
				strSQL += "  TRIM (a.acname) acct_name,TRIM (a.ddctyp) acct_ccy,TRIM (a.cifno) cif_no,TRIM (a.status) acct_status,";
				strSQL += "  TRIM (a.datop6) date_acct_opened,TRIM (a.datop7) issued_date,TRIM (a.rate) interest_rate,TRIM(a.accrue) accured_interest,TRIM('') accured_interest_due, TRIM (a.odlimt) overdraft_limit,";
				strSQL += "  TRIM (a.hold || ':' || a.cbal || ':' || (a.cbal - a.hold) || ':' || a.odlimt || ':' || a.whhirt) all_bal,(SELECT TRIM(tmbmrser) FROM stdattrn.tmpbmast WHERE tmbmacct = a.acctno) passbook_no,";
				strSQL += "  (SELECT TRIM(sub.hold || ':' || sub.cbal || ':' || (sub.cbal - sub.hold) || ':' || sub.odlimt || ':' || a.whhirt) inner_bal";
				strSQL += "  FROM stdattrn.ddmemo sub WHERE sub.acctno = '" + acct_no + "' AND a.acctno = sub.acctno AND a.cifno = sub.cifno AND a.status <> '2') memo_bal";
				strSQL += "  FROM stdattrn.ddtnew a,stdattrn.cfacct b WHERE b.cfaccn = '" + acct_no + "'";
				strSQL += "  AND a.acctno = b.cfaccn AND a.cifno = b.cfcifn AND a.status <> '2') mst";
			} else if (acct_type.equals(Global.COREBBANK_LN_ACCOUNT_TYPE)) {
				strSQL += "SELECT mst.bank_no,mst.branch_no,mst.acct_no,'' p_acct_no,mst.acct_type,mst.acct_name,mst.acct_ccy,mst.cif_no,";
				strSQL += "  mst.acct_status,mst.issued_date,mst.date_acct_opened,mst.maturity_date,'' establish_date,mst.full_release_date,";
				strSQL += "  COALESCE (mst.memo_bal, mst.all_bal) real_bal,mst.product_code,mst.term,mst.term_type,'' passbook_no,";
				strSQL += "  '0' interest_rate,mst.accured_interest,'' accured_interest_due,mst.loan_no";
				strSQL += "  FROM (SELECT '032' bank_no,TRIM (a.brn) branch_no,TRIM (a.cifno) cif_no,TRIM (a.acname) acct_name,";
				strSQL += "  TRIM (a.acctno) acct_no,TRIM (a.actype) acct_type,TRIM (a.curtyp) acct_ccy,TRIM (a.status) acct_status,";
				strSQL += "  TRIM (a.lnnum) loan_no,TRIM (a.datopn) date_acct_opened,TRIM (a.datopn) issued_date,TRIM (a.purcod) purpose_code,";
				strSQL += "  TRIM (a.fulldt) full_release_date,TRIM (a.matdt) maturity_date,TRIM (a.odind) overdue_indicator_description,";
				strSQL += "  TRIM (a.TYPE) product_code,TRIM (a.freq) principal_frequent,TRIM(prterm) term,TRIM(prcode) term_type,TRIM (a.ipfreq) interest_frequent,";
				strSQL += "  TRIM (a.orgamt) original_balance,TRIM (a.cbal) os_principal,TRIM (a.orgamt - a.amtrel) available_limit,";
				strSQL += "  TRIM (a.ysobal + a.billco) os_balance,TRIM(  a.bilprn + a.bilint + a.billc + a.bilesc + a.biloc + a.bilmc)billed_total_amount,";
				strSQL += "  TRIM (a.bilprn) billed_principal,TRIM (a.bilint) billed_interest,TRIM (a.billc) billed_late_charge,";
				strSQL += "  TRIM (a.othchg) other_charges,TRIM (a.drlimt) overdraft_limit,TRIM (a.hold) hold_amount,TRIM (a.ysacci + a.accint) accured_interest,";
				strSQL += "  TRIM (a.comacc) accrued_common_fee,TRIM (a.accmlc) accrued_late_charge,TRIM (a.orgamt - a.amtrel) available_limit,";
				strSQL += "  TRIM (a.hold) earmarked_amt,TRIM (a.pmtamt) payment_amount,TRIM (a.fnlpmt) final_payment_amt,";
				strSQL += "  (TRIM (a.orgamt) || ':' || TRIM (a.cbal) || ':' || TRIM (a.orgamt - a.amtrel) || ':' || TRIM (a.ysobal + a.billco)";
				strSQL += "  || ':' || TRIM(a.bilprn + a.bilint + a.billc + a.bilesc + a.biloc + a.bilmc) || ':' || TRIM (a.bilprn) || ':' || ";
				strSQL += "  TRIM (a.bilint) || ':' || TRIM (a.billc) || ':' || TRIM (a.othchg) || ':' || TRIM (a.drlimt) || ':' || ";
				strSQL += "  TRIM (a.hold) || ':' || TRIM (a.accint) || ':' || TRIM (a.comacc) || ':' ||  TRIM (a.accmlc) || ':' || ";
				strSQL += "  TRIM (a.orgamt - a.amtrel) || ':' || TRIM (a.hold) || ':' || TRIM (a.pmtamt) || ':' || TRIM (a.fnlpmt)) all_bal,";
				strSQL += "  (SELECT (TRIM (a.orgamt) || ':' || TRIM (sub.cbal) || ':' || TRIM (a.orgamt - a.amtrel) || ':' || ";
				strSQL += "  TRIM (a.ysobal + a.billco) || ':' || TRIM(sub.bilprn + sub.bilint + sub.billc + sub.bilesc + sub.biloc + sub.bilmc)";
				strSQL += "  || ':' || TRIM (sub.bilprn) || ':' || TRIM (sub.bilint) || ':' || TRIM (sub.billc) || ':' || TRIM (sub.othchg) || ':' ||";
				strSQL += "  TRIM (sub.drlimt) || ':' || TRIM (sub.hold) || ':' || TRIM (sub.accint) || ':' || TRIM (sub.comacc) || ':' || TRIM (a.accmlc) || ':' ||";
				strSQL += "  TRIM (a.orgamt - sub.amtrel) || ':' || TRIM (sub.hold) || ':' || TRIM (a.pmtamt) || ':' || TRIM (a.fnlpmt))";
				strSQL += "  inner_bal FROM  stdattrn.lnmemo sub WHERE a.acctno = sub.acctno AND a.status <> '2') memo_bal";
				strSQL += "  FROM   stdattrn.lnmast a, stdattrn.cfacct b WHERE b.cfaccn = '" + acct_no + "'";
				strSQL += "  AND a.acctno = b.cfaccn AND a.cifno = b.cfcifn AND a.status <> '2') mst";
				strSQL += " UNION ALL ";
				strSQL += "SELECT mst.bank_no,mst.branch_no,mst.acct_no,'' p_acct_no,mst.acct_type,mst.acct_name,mst.acct_ccy,";
				strSQL += "  mst.cif_no,mst.acct_status,mst.issued_date,mst.date_acct_opened,mst.maturity_date,'' establish_date,mst.full_release_date,";
				strSQL += "  COALESCE (mst.memo_bal, mst.all_bal) real_bal,mst.product_code,mst.term,mst.term_type,'' passbook_no,'0' interest_rate,";
				strSQL += "  mst.accured_interest,'' accured_interest_due,mst.loan_no";
				strSQL += "  FROM (SELECT '032' bank_no,TRIM (a.brn) branch_no,TRIM (a.cifno) cif_no,TRIM (a.acname) acct_name,TRIM (a.acctno) acct_no,";
				strSQL += "  TRIM (a.actype) acct_type,TRIM (a.curtyp) acct_ccy,TRIM (a.status) acct_status,TRIM (a.lnnum) loan_no,";
				strSQL += "  TRIM (a.datopn) date_acct_opened,TRIM (a.datopn) issued_date,TRIM (a.purcod) purpose_code,TRIM (a.fulldt) full_release_date,";
				strSQL += "  TRIM (a.matdt) maturity_date,TRIM (a.odind) overdue_indicator_description, TRIM (a.TYPE) product_code, TRIM (a.freq) principal_frequent,";
				strSQL += "  TRIM(prterm) term,TRIM(prcode) term_type,TRIM (a.ipfreq) interest_frequent,TRIM (a.orgamt) original_balance,TRIM (a.cbal) os_principal,TRIM (a.orgamt - a.amtrel) available_limit,";
				strSQL += "  TRIM (a.ysobal + a.billco) os_balance,TRIM (a.bilprn + a.bilint + a.billc + a.bilesc + a.biloc + a.bilmc) billed_total_amount,";
				strSQL += "  TRIM (a.bilprn) billed_principal,TRIM (a.bilint) billed_interest,TRIM (a.billc) billed_late_charge,TRIM (a.othchg) other_charges,";
				strSQL += "  TRIM (a.drlimt) overdraft_limit,TRIM (a.hold) hold_amount,TRIM (a.ysacci + a.accint) accured_interest,TRIM (a.comacc) accrued_common_fee,";
				strSQL += "  TRIM (a.accmlc) accrued_late_charge,TRIM (a.orgamt - a.amtrel) available_limit,TRIM (a.hold) earmarked_amt,TRIM (a.pmtamt) payment_amount,";
				strSQL += "  TRIM (a.fnlpmt) final_payment_amt,(TRIM (a.orgamt) || ':' || TRIM (a.cbal) || ':' || TRIM (a.orgamt - a.amtrel) || ':'||";
				strSQL += "  TRIM (a.ysobal + a.billco) || ':' || TRIM (a.bilprn+ a.bilint+ a.billc+ a.bilesc+ a.biloc+ a.bilmc) || ':' ||";
				strSQL += "  TRIM (a.bilprn) || ':' || TRIM (a.bilint) || ':' || TRIM (a.billc) || ':' || TRIM (a.othchg) || ':' ||";
				strSQL += "  TRIM (a.drlimt) || ':' || TRIM (a.hold) || ':' || TRIM (a.accint) || ':' || TRIM (a.comacc) || ':' ||";
				strSQL += "  TRIM (a.accmlc) || ':' || TRIM (a.orgamt - a.amtrel) || ':' || TRIM (a.hold) || ':' || TRIM (a.pmtamt) || ':' ||";
				strSQL += "  TRIM (a.fnlpmt)) all_bal, (SELECT (TRIM (a.orgamt) || ':' || TRIM (sub.cbal) || ':' || TRIM (a.orgamt - a.amtrel) || ':' || ";
				strSQL += "  TRIM (a.ysobal + a.billco) || ':' || TRIM(sub.bilprn + sub.bilint + sub.billc + sub.bilesc + sub.biloc + sub.bilmc)";
				strSQL += "  || ':' || TRIM (sub.bilprn) || ':' || TRIM (sub.bilint) || ':' || TRIM (sub.billc) || ':' || TRIM (sub.othchg) || ':' ||";
				strSQL += "  TRIM (sub.drlimt) || ':' || TRIM (sub.hold) || ':' || TRIM (sub.accint) || ':' || TRIM (sub.comacc) || ':' || TRIM (a.accmlc) || ':' ||";
				strSQL += "  TRIM (a.orgamt - sub.amtrel) || ':' || TRIM (sub.hold) || ':' || TRIM (a.pmtamt) || ':' || TRIM (a.fnlpmt))";
				strSQL += "  inner_bal FROM  stdattrn.lnmemo sub WHERE a.acctno = sub.acctno AND a.status <> '2') memo_bal";
				strSQL += "  FROM  stdattrn.lntnew a, stdattrn.cfacct b WHERE b.cfaccn = '" + acct_no + "' AND a.acctno = b.cfaccn";
				strSQL += "  AND a.cifno = b.cfcifn AND a.status <> '2') mst ";
			} else if (acct_type.equals(Global.COREBBANK_FD_RECEIPT_ACCOUNT_TYPE)) {
				strSQL += "SELECT mst.bank_no,mst.branch_no,mst.acct_no,mst.p_acct_no,mst.acct_type,mst.acct_name,mst.acct_ccy,mst.cif_no,";
				strSQL += "  mst.acct_status,mst.issued_date,mst.issued_date date_acct_opened,mst.maturity_date,mst.establish_date,";
				strSQL += "  '' full_release_date,COALESCE (mst.memo_bal, mst.all_bal) real_bal,product_code,term,term_type,";
				strSQL += "  mst.passbook_no,mst.interest_rate,mst.accured_interest,'' accured_interest_due,'' loan_no";
				strSQL += "  FROM (SELECT '032' bank_no,TRIM (a.actype) acct_type,TRIM (a.acctno) acct_no,TRIM (a.brn) branch_no,";
				strSQL += "  TRIM (a.cifno) cif_no,TRIM (a.acname) acct_name,TRIM (a.cdterm) term,TRIM (a.cdtcod) term_type,";
				strSQL += "  TRIM (a.curtyp) acct_ccy,TRIM (a.orgbal) ori_balance,TRIM (a.cbal) principal_balance,TRIM (a.accint) accured_interest,";
				strSQL += "  TRIM (a.penamt) penalty_amt,TRIM (a.hold) hold_amt,TRIM(a.orgbal || ':' || a.cbal || ':' || a.accint";
				strSQL += "  || ':' || a.penamt || ':' || a.hold || ':' || (TRIM (cbal + accint - penamt - hold - wdrwh))) all_bal,TRIM(a.cdrsrn) passbook_no,";
				strSQL += "  (a.cbal + a.accint - a.penamt - a.hold - a.wdrwh) current_cash_value, TRIM (a.issdt6) issued_date,";
				strSQL += "  TRIM (a.matdt6) maturity_date,TRIM (a.statd6) establish_date,TRIM (a.status) acct_status,TRIM (a.rate) interest_rate,";
				strSQL += "  TRIM (a.wdrwh),TRIM (a.cdnum) p_acct_no,TRIM (a.rnwctr),TRIM (a.renew),TRIM (a.dactn),TRIM (a.TYPE) product_code,";
				strSQL += "  (SELECT TRIM(a.orgbal || ':' || a.cbal || ':' || a.accint || ':' || a.penamt || ':' || a.hold || ':' || ";
				strSQL += "  (TRIM(cbal + accint - penamt - hold - wdrwh)))inner_bal FROM stdattrn.cdmemo sub ";
				strSQL += "  WHERE  a.acctno = sub.acctno AND a.status <> '2') memo_bal FROM stdattrn.cdmast a,stdattrn.cfacct b";
				strSQL += "  WHERE a.acctno = '" + acct_no + "' AND a.acctno = b.cfaccn AND a.cifno = b.cfcifn AND a.status <> '2') mst ";
				strSQL += " UNION ALL ";
				strSQL += "SELECT mst.bank_no,mst.branch_no,mst.acct_no,mst.p_acct_no,mst.acct_type,mst.acct_name,mst.acct_ccy,mst.cif_no,";
				strSQL += "  mst.acct_status,mst.issued_date,mst.issued_date date_acct_opened,mst.maturity_date,mst.establish_date,";
				strSQL += "  '' full_release_date,COALESCE (mst.memo_bal, mst.all_bal) real_bal,mst.product_code,mst.term,mst.term_type,";
				strSQL += "  mst.passbook_no,mst.interest_rate,mst.accured_interest,'' accured_interest_due,'' loan_no";
				strSQL += "  FROM (SELECT '032' bank_no,TRIM (a.actype) acct_type,TRIM (a.acctno) acct_no,TRIM (a.brn) branch_no,";
				strSQL += "  TRIM (a.cifno) cif_no,TRIM (a.acname) acct_name,TRIM (a.cdterm) term,TRIM (a.cdtcod) term_type,";
				strSQL += "  TRIM (a.curtyp) acct_ccy,TRIM (a.orgbal) ori_balance,TRIM (a.cbal) principal_balance,TRIM (a.accint) accured_interest,";
				strSQL += "  TRIM (a.penamt) penalty_amt,TRIM (a.hold) hold_amt,TRIM (a.orgbal || ':' || a.cbal || ':' || a.accint || ':' || a.penamt || ':' || a.hold || ':'";
				strSQL += "  || (TRIM (cbal + accint - penamt - hold - wdrwh))) all_bal,TRIM(a.cdrsrn) passbook_no, (a.cbal + a.accint - a.penamt - a.hold - a.wdrwh) current_cash_value,";
				strSQL += "  TRIM (a.issdt6) issued_date,TRIM (a.matdt6) maturity_date,TRIM (a.statd6) establish_date,TRIM (a.status) acct_status,";
				strSQL += "  TRIM (a.rate) interest_rate,TRIM (a.wdrwh),TRIM (a.cdnum) p_acct_no,TRIM (a.rnwctr),TRIM (a.renew),TRIM (a.dactn),";
				strSQL += "  TRIM (a.TYPE) product_code,(SELECT (a.orgbal || ':' || sub.cbal || ':' || sub.accint || ':' || sub.penamt || ':'";
				strSQL += "  || sub.hold || ':' || (TRIM (cbal + accint - penamt - hold - wdrwh))) inner_bal";
				strSQL += "  FROM stdattrn.cdmemo sub WHERE a.acctno = sub.acctno AND a.status <> '2') memo_bal";
				strSQL += "  FROM stdattrn.cdtnew a,stdattrn.cfacct b WHERE a.acctno = '" + acct_no + "'";
				strSQL += "  AND a.acctno = b.cfaccn AND a.cifno = b.cfcifn AND a.status <> '2') mst";
			} else if (acct_type.equals(Global.COREBBANK_FD_GROUP_ACCOUNT_TYPE)) {
				// FD group
				strSQL += " SELECT '032' bank_no,'' branch_no,TRIM (a.cfagno) acct_no,'' p_acct_no,'G' acct_type,TRIM (a.cfgnam) acct_name,";
				strSQL += " TRIM (a.cfgcur) acct_ccy,TRIM (a.cfcifn) cif_no,'' acct_status,'' issued_date,TRIM (a.cfagd7) date_acct_opened,";
				strSQL += " TRIM (a.cfcmd7) maturity_date,'' establish_date,'' full_release_date,";
				strSQL += " (a.cfgrat || ':' || a.cfggbl || ':' || a.cfgpbl || ':' || a.cfgint || ':' || a.cfgpen || ':' ||";
				strSQL += " a.cfghld || ':' || a.cfgflt || ':' || a.cfgcft) real_bal,'' product_code,'' term,'' term_type,'' passbook_no,";
				strSQL += " TRIM(a.cfgrat) interest_rate,TRIM (a.cfgint) accured_interest,'' accured_interest_due,'' loan_no FROM stdattrn.cfagrp a";
				strSQL += " WHERE a.cfagno = '" + acct_no + "'";
			}
			pst1 = connToSIBS.prepareStatement(strSQL);
			ResultSet rs = pst1.executeQuery();
			while (rs != null && rs.next()) {
				temAccountInfo = new AccountInfo();
				temAccountInfo.setBank_no(AppUtils.correctStringValue(rs.getString("bank_no")));
				temAccountInfo.setBranch_no(AppUtils.correctStringValue(rs.getString("branch_no")));
				temAccountInfo.setAccount_no(AppUtils.correctStringValue(rs.getString("acct_no")));
				temAccountInfo.setP_acct_no(AppUtils.correctStringValue(rs.getString("p_acct_no")));
				temAccountInfo.setAcct_type(AppUtils.correctStringValue(rs.getString("acct_type")));
				temAccountInfo.setAcct_name(AppUtils.correctStringValue(rs.getString("acct_name")));
				temAccountInfo.setAcct_ccy(AppUtils.correctStringValue(rs.getString("acct_ccy")));
				temAccountInfo.setCif_no(AppUtils.correctStringValue(rs.getString("cif_no")));
				temAccountInfo.setAcct_status(AppUtils.correctStringValue(rs.getString("acct_status")));
				temAccountInfo.setIssued_date(AppUtils.correctStringValue(rs.getString("issued_date")));
				temAccountInfo.setDate_acct_opened(AppUtils.correctStringValue(rs.getString("date_acct_opened")));
				temAccountInfo.setMaturity_date(AppUtils.correctStringValue(rs.getString("maturity_date")));
				temAccountInfo.setEstablish_date(AppUtils.correctStringValue(rs.getString("establish_date")));
				temAccountInfo.setFull_release_date(AppUtils.correctStringValue(rs.getString("full_release_date")));
				temAccountInfo.setProduct_code(AppUtils.correctStringValue(rs.getString("product_code")));
				temAccountInfo.setTerm(AppUtils.correctStringValue(rs.getString("term")));
				temAccountInfo.setAccured_interest_due(AppUtils.correctStringValue(rs.getString("accured_interest_due")));
				processBalance(temAccountInfo, temAccountInfo.getAcct_type(), rs.getString("real_bal"));
				temAccountInfo.setPassbook_no(AppUtils.correctStringValue(rs.getString("passbook_no")));
				temAccountInfo.setInterest_rate(AppUtils.correctStringValue(rs.getString("interest_rate")));
				temAccountInfo.setAccured_interest(AppUtils.correctStringValue(rs.getString("accured_interest")));
				temAccountInfo.setBranch_no(AppUtils.correctStringValue(rs.getString("loan_no")));
			}
		} catch (SQLException e) {
			if (connToSIBS != null) {
				try {
					connToSIBS.rollback();
				} catch (SQLException excep) {
					excep.printStackTrace();
				}
			}
		} finally {
			try {
				if (pst1 != null) {
					pst1.close();
				}
				connToSIBS.setAutoCommit(true);
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return temAccountInfo;
	}

	@Override
	public ArrayList<AccountInfo> getAccountInfoByCif(String cif_no) {
		Connection connToSIBS = null;
		PreparedStatement pst1 = null;
		ArrayList<AccountInfo> arrAccountInfo = new ArrayList<AccountInfo>();
		try {
			connToSIBS = getDb2CoreJDBC("10.0.1.1", "salarydele", "salarydele");
			String strSQL = "";
			strSQL += "SELECT mst.bank_no,mst.branch_no,mst.acct_no,'' p_acct_no,mst.acct_type,mst.acct_name,mst.acct_ccy, ";
			strSQL += "  mst.cif_no,mst.acct_status,mst.issued_date,mst.date_acct_opened,'' maturity_date,'' establish_date, ";
			strSQL += "  '' full_release_date,COALESCE (mst.memo_bal, mst.all_bal) real_bal,'' product_code,'' term,'' term_type, ";
			strSQL += "  mst.passbook_no,mst.interest_rate,mst.accured_interest,mst.accured_interest_due,'' loan_no ";
			strSQL += "  FROM (SELECT '032' bank_no,TRIM (a.branch) branch_no,TRIM (a.acctno) acct_no,TRIM (a.actype) acct_type, ";
			strSQL += "  TRIM (a.acname) acct_name,TRIM (a.ddctyp) acct_ccy,TRIM (a.cifno) cif_no,TRIM (a.status) acct_status, ";
			strSQL += "  TRIM (a.datop6) date_acct_opened,TRIM (a.datop7) issued_date,TRIM (a.rate) interest_rate,TRIM(a.accrue) accured_interest,TRIM('') accured_interest_due,TRIM (a.odlimt) overdraft_limit, ";
			strSQL += "  TRIM (a.hold || ':' || a.cbal || ':' || (a.cbal - a.hold) || ':' || a.odlimt || ':' || a.whhirt) all_bal,(SELECT TRIM(tmbmrser) FROM stdattrn.tmpbmast WHERE tmbmacct = a.acctno) passbook_no, ";
			strSQL += "  (SELECT TRIM(sub.hold || ':' || sub.cbal || ':' || (sub.cbal - sub.hold) || ':' || sub.odlimt || ':' || a.whhirt) inner_bal ";
			strSQL += "  FROM  stdattrn.ddmemo sub WHERE sub.cifno = '" + cif_no + "' AND a.acctno = sub.acctno AND a.cifno = sub.cifno ";
			strSQL += "  AND a.status <> '2') memo_bal FROM stdattrn.ddmast a,stdattrn.cfacct b ";
			strSQL += "  WHERE b.cfcifn = '" + cif_no + "' AND a.acctno = b.cfaccn AND a.cifno = b.cfcifn AND a.status <> '2') mst ";
			strSQL += "  UNION ALL ";
			strSQL += "SELECT mst.bank_no,mst.branch_no,mst.acct_no,'' p_acct_no,mst.acct_type,mst.acct_name,mst.acct_ccy,";
			strSQL += "  mst.cif_no,mst.acct_status,mst.issued_date,mst.date_acct_opened,'' maturity_date,'' establish_date,";
			strSQL += "  '' full_release_date,COALESCE (mst.memo_bal, mst.all_bal) real_bal,'' product_code,'' term,'' term_type,";
			strSQL += "  mst.passbook_no,mst.interest_rate,mst.accured_interest,mst.accured_interest_due,'' loan_no";
			strSQL += "  FROM (SELECT '032' bank_no,TRIM (a.branch) branch_no,TRIM (a.acctno) acct_no,TRIM (a.actype) acct_type,";
			strSQL += "  TRIM (a.acname) acct_name,TRIM (a.ddctyp) acct_ccy,TRIM (a.cifno) cif_no,TRIM (a.status) acct_status,";
			strSQL += "  TRIM (a.datop6) date_acct_opened,TRIM (a.datop7) issued_date,TRIM (a.rate) interest_rate,TRIM(a.accrue) accured_interest,TRIM('') accured_interest_due, TRIM (a.odlimt) overdraft_limit,";
			strSQL += "  TRIM (a.hold || ':' || a.cbal || ':' || (a.cbal - a.hold) || ':' || a.odlimt || ':' || a.whhirt) all_bal,(SELECT TRIM(tmbmrser) FROM stdattrn.tmpbmast WHERE tmbmacct = a.acctno) passbook_no,";
			strSQL += "  (SELECT TRIM(sub.hold || ':' || sub.cbal || ':' || (sub.cbal - sub.hold) || ':' || sub.odlimt || ':' || a.whhirt) inner_bal";
			strSQL += "  FROM stdattrn.ddmemo sub WHERE sub.cifno = '" + cif_no + "' AND a.acctno = sub.acctno AND a.cifno = sub.cifno AND a.status <> '2') memo_bal";
			strSQL += "  FROM stdattrn.ddtnew a,stdattrn.cfacct b WHERE b.cfcifn = '" + cif_no + "'";
			strSQL += "  AND a.acctno = b.cfaccn AND a.cifno = b.cfcifn AND a.status <> '2') mst";

			strSQL += " UNION ALL ";

			// strSQL +=
			// "SELECT mst.bank_no,mst.branch_no,mst.acct_no,'' p_acct_no,mst.acct_type,mst.acct_name,mst.acct_ccy,mst.cif_no,";
			// strSQL +=
			// "  mst.acct_status,mst.issued_date,mst.date_acct_opened,mst.maturity_date,'' establish_date,mst.full_release_date,";
			// strSQL +=
			// "  COALESCE (mst.memo_bal, mst.all_bal) real_bal,mst.product_code,mst.term,mst.term_type,'' passbook_no,";
			// strSQL +=
			// "  '0' interest_rate,mst.accured_interest,'' accured_interest_due,mst.loan_no";
			// strSQL +=
			// "  FROM (SELECT '032' bank_no,TRIM (a.brn) branch_no,TRIM (a.cifno) cif_no,TRIM (a.acname) acct_name,";
			// strSQL +=
			// "  TRIM (a.acctno) acct_no,TRIM (a.actype) acct_type,TRIM (a.curtyp) acct_ccy,TRIM (a.status) acct_status,";
			// strSQL +=
			// "  TRIM (a.lnnum) loan_no,TRIM (a.datopn) date_acct_opened,TRIM (a.datopn) issued_date,TRIM (a.purcod) purpose_code,";
			// strSQL +=
			// "  TRIM (a.fulldt) full_release_date,TRIM (a.matdt) maturity_date,TRIM (a.odind) overdue_indicator_description,";
			// strSQL +=
			// "  TRIM (a.TYPE) product_code,TRIM (a.freq) principal_frequent,TRIM(prterm) term,TRIM(prcode) term_type,TRIM (a.ipfreq) interest_frequent,";
			// strSQL +=
			// "  TRIM (a.orgamt) original_balance,TRIM (a.cbal) os_principal,TRIM (a.orgamt - a.amtrel) available_limit,";
			// strSQL +=
			// "  TRIM (a.ysobal + a.billco) os_balance,TRIM(  a.bilprn + a.bilint + a.billc + a.bilesc + a.biloc + a.bilmc)billed_total_amount,";
			// strSQL +=
			// "  TRIM (a.bilprn) billed_principal,TRIM (a.bilint) billed_interest,TRIM (a.billc) billed_late_charge,";
			// strSQL +=
			// "  TRIM (a.othchg) other_charges,TRIM (a.drlimt) overdraft_limit,TRIM (a.hold) hold_amount,TRIM (a.ysacci + a.accint) accured_interest,";
			// strSQL +=
			// "  TRIM (a.comacc) accrued_common_fee,TRIM (a.accmlc) accrued_late_charge,TRIM (a.orgamt - a.amtrel) available_limit,";
			// strSQL +=
			// "  TRIM (a.hold) earmarked_amt,TRIM (a.pmtamt) payment_amount,TRIM (a.fnlpmt) final_payment_amt,";
			// strSQL +=
			// "  (TRIM (a.orgamt) || ':' || TRIM (a.cbal) || ':' || TRIM (a.orgamt - a.amtrel) || ':' || TRIM (a.ysobal + a.billco)";
			// strSQL +=
			// "  || ':' || TRIM(a.bilprn + a.bilint + a.billc + a.bilesc + a.biloc + a.bilmc) || ':' || TRIM (a.bilprn) || ':' || ";
			// strSQL +=
			// "  TRIM (a.bilint) || ':' || TRIM (a.billc) || ':' || TRIM (a.othchg) || ':' || TRIM (a.drlimt) || ':' || ";
			// strSQL +=
			// "  TRIM (a.hold) || ':' || TRIM (a.accint) || ':' || TRIM (a.comacc) || ':' ||  TRIM (a.accmlc) || ':' || ";
			// strSQL +=
			// "  TRIM (a.orgamt - a.amtrel) || ':' || TRIM (a.hold) || ':' || TRIM (a.pmtamt) || ':' || TRIM (a.fnlpmt)) all_bal,";
			// strSQL +=
			// "  (SELECT (TRIM (a.orgamt) || ':' || TRIM (a.cbal) || ':' || TRIM (a.orgamt - a.amtrel) || ':' ||";
			// strSQL += "  TRIM (a.ysobal + a.billco) || ':' ||";
			// strSQL +=
			// "  TRIM (a.bilprn + a.bilint + a.billc + a.bilesc + a.biloc + a.bilmc) || ':' || TRIM (a.bilprn) || ':' ||";
			// strSQL +=
			// "  TRIM (a.bilint) || ':' || TRIM (a.billc) || ':' || TRIM (a.othchg) || ':' || TRIM (a.drlimt) || ':' ||";
			// strSQL +=
			// "  TRIM (a.hold) || ':' || TRIM (a.accint) || ':' || TRIM (a.comacc) || ':' || TRIM (a.accmlc) || ':' ||";
			// strSQL +=
			// "  TRIM (a.orgamt - a.amtrel) || ':' || TRIM (a.hold) || ':' || TRIM (a.pmtamt) || ':' || TRIM (a.fnlpmt)) inner_bal";
			// strSQL +=
			// "  FROM stdattrn.lnmemo sub WHERE a.acctno = sub.acctno AND a.status <> '2') memo_bal";
			// strSQL +=
			// "  FROM   stdattrn.lnmast a, stdattrn.cfacct b WHERE b.cfcifn = '"
			// + cif_no + "'";
			// strSQL +=
			// "  AND a.acctno = b.cfaccn AND a.cifno = b.cfcifn AND a.status <> '2') mst";
			// strSQL += " UNION ALL ";
			// strSQL +=
			// "SELECT mst.bank_no,mst.branch_no,mst.acct_no,'' p_acct_no,mst.acct_type,mst.acct_name,mst.acct_ccy,";
			// strSQL +=
			// "  mst.cif_no,mst.acct_status,mst.issued_date,mst.date_acct_opened,mst.maturity_date,'' establish_date,mst.full_release_date,";
			// strSQL +=
			// "  COALESCE (mst.memo_bal, mst.all_bal) real_bal,mst.product_code,mst.term,mst.term_type,'' passbook_no,'0' interest_rate,";
			// strSQL +=
			// "  mst.accured_interest,'' accured_interest_due,mst.loan_no";
			// strSQL +=
			// "  FROM (SELECT '032' bank_no,TRIM (a.brn) branch_no,TRIM (a.cifno) cif_no,TRIM (a.acname) acct_name,TRIM (a.acctno) acct_no,";
			// strSQL +=
			// "  TRIM (a.actype) acct_type,TRIM (a.curtyp) acct_ccy,TRIM (a.status) acct_status,TRIM (a.lnnum) loan_no,";
			// strSQL +=
			// "  TRIM (a.datopn) date_acct_opened,TRIM (a.datopn) issued_date,TRIM (a.purcod) purpose_code,TRIM (a.fulldt) full_release_date,";
			// strSQL +=
			// "  TRIM (a.matdt) maturity_date,TRIM (a.odind) overdue_indicator_description, TRIM (a.TYPE) product_code, TRIM (a.freq) principal_frequent,";
			// strSQL +=
			// "  TRIM(prterm) term,TRIM(prcode) term_type,TRIM (a.ipfreq) interest_frequent,TRIM (a.orgamt) original_balance,TRIM (a.cbal) os_principal,TRIM (a.orgamt - a.amtrel) available_limit,";
			// strSQL +=
			// "  TRIM (a.ysobal + a.billco) os_balance,TRIM (a.bilprn + a.bilint + a.billc + a.bilesc + a.biloc + a.bilmc) billed_total_amount,";
			// strSQL +=
			// "  TRIM (a.bilprn) billed_principal,TRIM (a.bilint) billed_interest,TRIM (a.billc) billed_late_charge,TRIM (a.othchg) other_charges,";
			// strSQL +=
			// "  TRIM (a.drlimt) overdraft_limit,TRIM (a.hold) hold_amount,TRIM (a.ysacci + a.accint) accured_interest,TRIM (a.comacc) accrued_common_fee,";
			// strSQL +=
			// "  TRIM (a.accmlc) accrued_late_charge,TRIM (a.orgamt - a.amtrel) available_limit,TRIM (a.hold) earmarked_amt,TRIM (a.pmtamt) payment_amount,";
			// strSQL +=
			// "  TRIM (a.fnlpmt) final_payment_amt,(TRIM (a.orgamt) || ':' || TRIM (a.cbal) || ':' || TRIM (a.orgamt - a.amtrel) || ':'||";
			// strSQL +=
			// "  TRIM (a.ysobal + a.billco) || ':' || TRIM (a.bilprn+ a.bilint+ a.billc+ a.bilesc+ a.biloc+ a.bilmc) || ':' ||";
			// strSQL +=
			// "  TRIM (a.bilprn) || ':' || TRIM (a.bilint) || ':' || TRIM (a.billc) || ':' || TRIM (a.othchg) || ':' ||";
			// strSQL +=
			// "  TRIM (a.drlimt) || ':' || TRIM (a.hold) || ':' || TRIM (a.accint) || ':' || TRIM (a.comacc) || ':' ||";
			// strSQL +=
			// "  TRIM (a.accmlc) || ':' || TRIM (a.orgamt - a.amtrel) || ':' || TRIM (a.hold) || ':' || TRIM (a.pmtamt) || ':' ||";
			// strSQL +=
			// "  TRIM (a.fnlpmt)) all_bal, (SELECT (TRIM (a.orgamt) || ':' || TRIM (a.cbal) || ':' || TRIM (a.orgamt - a.amtrel) || ':' || ";
			// strSQL +=
			// "  TRIM (a.ysobal + a.billco) || ':' || TRIM(a.bilprn + a.bilint + a.billc + a.bilesc + a.biloc + a.bilmc)";
			// strSQL +=
			// "  || ':' || TRIM (a.bilprn) || ':' || TRIM (a.bilint) || ':' || TRIM (a.billc) || ':' || TRIM (a.othchg) || ':' ||";
			// strSQL +=
			// "  TRIM (a.drlimt) || ':' || TRIM (a.hold) || ':' || TRIM (a.accint) || ':' || TRIM (a.comacc) || ':' || TRIM (a.accmlc) || ':' ||";
			// strSQL +=
			// "  TRIM (a.orgamt - a.amtrel) || ':' || TRIM (a.hold) || ':' || TRIM (a.pmtamt) || ':' || TRIM (a.fnlpmt))";
			// strSQL +=
			// "  inner_bal FROM   stdattrn.lnmemo sub WHERE a.acctno = sub.acctno AND a.status <> '2') memo_bal";
			// strSQL +=
			// "  FROM  stdattrn.lntnew a, stdattrn.cfacct b WHERE b.cfcifn = '"
			// + cif_no + "' AND a.acctno = b.cfaccn";
			// strSQL += "  AND a.cifno = b.cfcifn AND a.status <> '2') mst ";

			strSQL += "SELECT mst.bank_no,mst.branch_no,mst.acct_no,'' p_acct_no,mst.acct_type,mst.acct_name,mst.acct_ccy,mst.cif_no,";
			strSQL += "  mst.acct_status,mst.issued_date,mst.date_acct_opened,mst.maturity_date,'' establish_date,mst.full_release_date,";
			strSQL += "  COALESCE (mst.memo_bal, mst.all_bal) real_bal,mst.product_code,mst.term,mst.term_type,'' passbook_no,";
			strSQL += "  '0' interest_rate,mst.accured_interest,'' accured_interest_due,mst.loan_no";
			strSQL += "  FROM (SELECT '032' bank_no,TRIM (a.brn) branch_no,TRIM (a.cifno) cif_no,TRIM (a.acname) acct_name,";
			strSQL += "  TRIM (a.acctno) acct_no,TRIM (a.actype) acct_type,TRIM (a.curtyp) acct_ccy,TRIM (a.status) acct_status,";
			strSQL += "  TRIM (a.lnnum) loan_no,TRIM (a.datopn) date_acct_opened,TRIM (a.datopn) issued_date,TRIM (a.purcod) purpose_code,";
			strSQL += "  TRIM (a.fulldt) full_release_date,TRIM (a.matdt) maturity_date,TRIM (a.odind) overdue_indicator_description,";
			strSQL += "  TRIM (a.TYPE) product_code,TRIM (a.freq) principal_frequent,TRIM(prterm) term,TRIM(prcode) term_type,TRIM (a.ipfreq) interest_frequent,";
			strSQL += "  TRIM (a.orgamt) original_balance,TRIM (a.cbal) os_principal,TRIM (a.orgamt - a.amtrel) available_limit,";
			strSQL += "  TRIM (a.ysobal + a.billco) os_balance,TRIM(  a.bilprn + a.bilint + a.billc + a.bilesc + a.biloc + a.bilmc)billed_total_amount,";
			strSQL += "  TRIM (a.bilprn) billed_principal,TRIM (a.bilint) billed_interest,TRIM (a.billc) billed_late_charge,";
			strSQL += "  TRIM (a.othchg) other_charges,TRIM (a.drlimt) overdraft_limit,TRIM (a.hold) hold_amount,TRIM (a.ysacci + a.accint) accured_interest,";
			strSQL += "  TRIM (a.comacc) accrued_common_fee,TRIM (a.accmlc) accrued_late_charge,TRIM (a.orgamt - a.amtrel) available_limit,";
			strSQL += "  TRIM (a.hold) earmarked_amt,TRIM (a.pmtamt) payment_amount,TRIM (a.fnlpmt) final_payment_amt,";
			strSQL += "  (TRIM (a.orgamt) || ':' || TRIM (a.cbal) || ':' || TRIM (a.orgamt - a.amtrel) || ':' || TRIM (a.ysobal + a.billco)";
			strSQL += "  || ':' || TRIM(a.bilprn + a.bilint + a.billc + a.bilesc + a.biloc + a.bilmc) || ':' || TRIM (a.bilprn) || ':' || ";
			strSQL += "  TRIM (a.bilint) || ':' || TRIM (a.billc) || ':' || TRIM (a.othchg) || ':' || TRIM (a.drlimt) || ':' || ";
			strSQL += "  TRIM (a.hold) || ':' || TRIM (a.accint) || ':' || TRIM (a.comacc) || ':' ||  TRIM (a.accmlc) || ':' || ";
			strSQL += "  TRIM (a.orgamt - a.amtrel) || ':' || TRIM (a.hold) || ':' || TRIM (a.pmtamt) || ':' || TRIM (a.fnlpmt)) all_bal,";
			strSQL += "  (SELECT (TRIM (a.orgamt) || ':' || TRIM (sub.cbal) || ':' || TRIM (a.orgamt - a.amtrel) || ':' || ";
			strSQL += "  TRIM (a.ysobal + a.billco) || ':' || TRIM(sub.bilprn + sub.bilint + sub.billc + sub.bilesc + sub.biloc + sub.bilmc)";
			strSQL += "  || ':' || TRIM (sub.bilprn) || ':' || TRIM (sub.bilint) || ':' || TRIM (sub.billc) || ':' || TRIM (sub.othchg) || ':' ||";
			strSQL += "  TRIM (sub.drlimt) || ':' || TRIM (sub.hold) || ':' || TRIM (sub.accint) || ':' || TRIM (sub.comacc) || ':' || TRIM (a.accmlc) || ':' ||";
			strSQL += "  TRIM (a.orgamt - sub.amtrel) || ':' || TRIM (sub.hold) || ':' || TRIM (a.pmtamt) || ':' || TRIM (a.fnlpmt))";
			strSQL += "  inner_bal FROM  stdattrn.lnmemo sub WHERE a.acctno = sub.acctno AND a.status <> '2') memo_bal";
			strSQL += "  FROM   stdattrn.lnmast a, stdattrn.cfacct b WHERE b.cfcifn = '" + cif_no + "'";
			strSQL += "  AND a.acctno = b.cfaccn AND a.cifno = b.cfcifn AND a.status <> '2') mst";
			strSQL += " UNION ALL ";
			strSQL += "SELECT mst.bank_no,mst.branch_no,mst.acct_no,'' p_acct_no,mst.acct_type,mst.acct_name,mst.acct_ccy,";
			strSQL += "  mst.cif_no,mst.acct_status,mst.issued_date,mst.date_acct_opened,mst.maturity_date,'' establish_date,mst.full_release_date,";
			strSQL += "  COALESCE (mst.memo_bal, mst.all_bal) real_bal,mst.product_code,mst.term,mst.term_type,'' passbook_no,'0' interest_rate,";
			strSQL += "  mst.accured_interest,'' accured_interest_due,mst.loan_no";
			strSQL += "  FROM (SELECT '032' bank_no,TRIM (a.brn) branch_no,TRIM (a.cifno) cif_no,TRIM (a.acname) acct_name,TRIM (a.acctno) acct_no,";
			strSQL += "  TRIM (a.actype) acct_type,TRIM (a.curtyp) acct_ccy,TRIM (a.status) acct_status,TRIM (a.lnnum) loan_no,";
			strSQL += "  TRIM (a.datopn) date_acct_opened,TRIM (a.datopn) issued_date,TRIM (a.purcod) purpose_code,TRIM (a.fulldt) full_release_date,";
			strSQL += "  TRIM (a.matdt) maturity_date,TRIM (a.odind) overdue_indicator_description, TRIM (a.TYPE) product_code, TRIM (a.freq) principal_frequent,";
			strSQL += "  TRIM(prterm) term,TRIM(prcode) term_type,TRIM (a.ipfreq) interest_frequent,TRIM (a.orgamt) original_balance,TRIM (a.cbal) os_principal,TRIM (a.orgamt - a.amtrel) available_limit,";
			strSQL += "  TRIM (a.ysobal + a.billco) os_balance,TRIM (a.bilprn + a.bilint + a.billc + a.bilesc + a.biloc + a.bilmc) billed_total_amount,";
			strSQL += "  TRIM (a.bilprn) billed_principal,TRIM (a.bilint) billed_interest,TRIM (a.billc) billed_late_charge,TRIM (a.othchg) other_charges,";
			strSQL += "  TRIM (a.drlimt) overdraft_limit,TRIM (a.hold) hold_amount,TRIM (a.ysacci + a.accint) accured_interest,TRIM (a.comacc) accrued_common_fee,";
			strSQL += "  TRIM (a.accmlc) accrued_late_charge,TRIM (a.orgamt - a.amtrel) available_limit,TRIM (a.hold) earmarked_amt,TRIM (a.pmtamt) payment_amount,";
			strSQL += "  TRIM (a.fnlpmt) final_payment_amt,(TRIM (a.orgamt) || ':' || TRIM (a.cbal) || ':' || TRIM (a.orgamt - a.amtrel) || ':'||";
			strSQL += "  TRIM (a.ysobal + a.billco) || ':' || TRIM (a.bilprn+ a.bilint+ a.billc+ a.bilesc+ a.biloc+ a.bilmc) || ':' ||";
			strSQL += "  TRIM (a.bilprn) || ':' || TRIM (a.bilint) || ':' || TRIM (a.billc) || ':' || TRIM (a.othchg) || ':' ||";
			strSQL += "  TRIM (a.drlimt) || ':' || TRIM (a.hold) || ':' || TRIM (a.accint) || ':' || TRIM (a.comacc) || ':' ||";
			strSQL += "  TRIM (a.accmlc) || ':' || TRIM (a.orgamt - a.amtrel) || ':' || TRIM (a.hold) || ':' || TRIM (a.pmtamt) || ':' ||";
			strSQL += "  TRIM (a.fnlpmt)) all_bal, (SELECT (TRIM (a.orgamt) || ':' || TRIM (sub.cbal) || ':' || TRIM (a.orgamt - a.amtrel) || ':' || ";
			strSQL += "  TRIM (a.ysobal + a.billco) || ':' || TRIM(sub.bilprn + sub.bilint + sub.billc + sub.bilesc + sub.biloc + sub.bilmc)";
			strSQL += "  || ':' || TRIM (sub.bilprn) || ':' || TRIM (sub.bilint) || ':' || TRIM (sub.billc) || ':' || TRIM (sub.othchg) || ':' ||";
			strSQL += "  TRIM (sub.drlimt) || ':' || TRIM (sub.hold) || ':' || TRIM (sub.accint) || ':' || TRIM (sub.comacc) || ':' || TRIM (a.accmlc) || ':' ||";
			strSQL += "  TRIM (a.orgamt - sub.amtrel) || ':' || TRIM (sub.hold) || ':' || TRIM (a.pmtamt) || ':' || TRIM (a.fnlpmt))";
			strSQL += "  inner_bal FROM  stdattrn.lnmemo sub WHERE a.acctno = sub.acctno AND a.status <> '2') memo_bal";
			strSQL += "  FROM  stdattrn.lntnew a, stdattrn.cfacct b WHERE b.cfcifn = '" + cif_no + "' AND a.acctno = b.cfaccn";
			strSQL += "  AND a.cifno = b.cfcifn AND a.status <> '2') mst ";

			strSQL += " UNION ALL ";
			//
			// // FD group
			// strSQL +=
			// " SELECT '032' bank_no,'' branch_no,TRIM (a.cfagno) acct_no,'' p_acct_no,'G' acct_type,TRIM (a.cfgnam) acct_name,";
			// strSQL +=
			// " TRIM (a.cfgcur) acct_ccy,TRIM (a.cfcifn) cif_no,'' acct_status,'' issued_date,TRIM (a.cfagd7) date_acct_opened,";
			// strSQL +=
			// " TRIM (a.cfcmd7) maturity_date,'' establish_date,'' full_release_date,";
			// strSQL +=
			// " (a.cfgrat || ':' || a.cfggbl || ':' || a.cfgpbl || ':' || a.cfgint || ':' || a.cfgpen || ':' ||";
			// strSQL +=
			// " a.cfghld || ':' || a.cfgflt || ':' || a.cfgcft) real_bal,'' product_code,'' term,'' term_type,'' passbook_no,";
			// strSQL +=
			// " TRIM(a.cfgrat) interest_rate,TRIM (a.cfgint) accured_interest,'' accured_interest_due,'' loan_no FROM stdattrn.cfagrp a";
			// strSQL += " WHERE a.cfcifn = '" + cif_no + "'";
			// strSQL += " UNION ALL ";

			// Fd account
			strSQL += "SELECT mst.bank_no,mst.branch_no,mst.acct_no,mst.p_acct_no,mst.acct_type,mst.acct_name,mst.acct_ccy,mst.cif_no,";
			strSQL += "  mst.acct_status,mst.issued_date,mst.issued_date date_acct_opened,mst.maturity_date,mst.establish_date,";
			strSQL += "  '' full_release_date,COALESCE (mst.memo_bal, mst.all_bal) real_bal,product_code,term,term_type,";
			strSQL += "  mst.passbook_no,mst.interest_rate,mst.accured_interest,'' accured_interest_due,'' loan_no";
			strSQL += "  FROM (SELECT '032' bank_no,TRIM (a.actype) acct_type,TRIM (a.acctno) acct_no,TRIM (a.brn) branch_no,";
			strSQL += "  TRIM (a.cifno) cif_no,TRIM (a.acname) acct_name,TRIM (a.cdterm) term,TRIM (a.cdtcod) term_type,";
			strSQL += "  TRIM (a.curtyp) acct_ccy,TRIM (a.orgbal) ori_balance,TRIM (a.cbal) principal_balance,TRIM (a.accint) accured_interest,";
			strSQL += "  TRIM (a.penamt) penalty_amt,TRIM (a.hold) hold_amt,TRIM(a.orgbal || ':' || a.cbal || ':' || a.accint";
			strSQL += "  || ':' || a.penamt || ':' || a.hold || ':' || (TRIM (cbal + accint - penamt - hold - wdrwh))) all_bal,TRIM(a.cdrsrn) passbook_no,";
			strSQL += "  (a.cbal + a.accint - a.penamt - a.hold - a.wdrwh) current_cash_value, TRIM (a.issdt6) issued_date,";
			strSQL += "  TRIM (a.matdt6) maturity_date,TRIM (a.statd6) establish_date,TRIM (a.status) acct_status,TRIM (a.rate) interest_rate,";
			strSQL += "  TRIM (a.wdrwh),TRIM (a.cdnum) p_acct_no,TRIM (a.rnwctr),TRIM (a.renew),TRIM (a.dactn),TRIM (a.TYPE) product_code,";
			strSQL += "  (SELECT TRIM(a.orgbal || ':' || a.cbal || ':' || a.accint || ':' || a.penamt || ':' || a.hold || ':' || ";
			strSQL += "  (TRIM(cbal + accint - penamt - hold - wdrwh)))inner_bal FROM stdattrn.cdmemo sub ";
			strSQL += "  WHERE  a.acctno = sub.acctno AND a.status <> '2') memo_bal FROM stdattrn.cdmast a,stdattrn.cfacct b";
			strSQL += "  WHERE b.cfcifn = '" + cif_no + "' AND a.acctno = b.cfaccn AND a.cifno = b.cfcifn AND a.status <> '2') mst ";
			strSQL += " UNION ALL ";
			strSQL += "SELECT mst.bank_no,mst.branch_no,mst.acct_no,mst.p_acct_no,mst.acct_type,mst.acct_name,mst.acct_ccy,mst.cif_no,";
			strSQL += "  mst.acct_status,mst.issued_date,mst.issued_date date_acct_opened,mst.maturity_date,mst.establish_date,";
			strSQL += "  '' full_release_date,COALESCE (mst.memo_bal, mst.all_bal) real_bal,mst.product_code,mst.term,mst.term_type,";
			strSQL += "  mst.passbook_no,mst.interest_rate,mst.accured_interest,'' accured_interest_due,'' loan_no";
			strSQL += "  FROM (SELECT '032' bank_no,TRIM (a.actype) acct_type,TRIM (a.acctno) acct_no,TRIM (a.brn) branch_no,";
			strSQL += "  TRIM (a.cifno) cif_no,TRIM (a.acname) acct_name,TRIM (a.cdterm) term,TRIM (a.cdtcod) term_type,";
			strSQL += "  TRIM (a.curtyp) acct_ccy,TRIM (a.orgbal) ori_balance,TRIM (a.cbal) principal_balance,TRIM (a.accint) accured_interest,";
			strSQL += "  TRIM (a.penamt) penalty_amt,TRIM (a.hold) hold_amt,TRIM (a.orgbal || ':' || a.cbal || ':' || a.accint || ':' || a.penamt || ':' || a.hold || ':'";
			strSQL += "  || (TRIM (cbal + accint - penamt - hold - wdrwh))) all_bal,TRIM(a.cdrsrn) passbook_no, (a.cbal + a.accint - a.penamt - a.hold - a.wdrwh) current_cash_value,";
			strSQL += "  TRIM (a.issdt6) issued_date,TRIM (a.matdt6) maturity_date,TRIM (a.statd6) establish_date,TRIM (a.status) acct_status,";
			strSQL += "  TRIM (a.rate) interest_rate,TRIM (a.wdrwh),TRIM (a.cdnum) p_acct_no,TRIM (a.rnwctr),TRIM (a.renew),TRIM (a.dactn),";
			strSQL += "  TRIM (a.TYPE) product_code,(SELECT (a.orgbal || ':' || sub.cbal || ':' || sub.accint || ':' || sub.penamt || ':'";
			strSQL += "  || sub.hold || ':' || (TRIM (cbal + accint - penamt - hold - wdrwh))) inner_bal";
			strSQL += "  FROM stdattrn.cdmemo sub WHERE a.acctno = sub.acctno AND a.status <> '2') memo_bal";
			strSQL += "  FROM stdattrn.cdtnew a,stdattrn.cfacct b WHERE b.cfcifn = '" + cif_no + "'";
			strSQL += "  AND a.acctno = b.cfaccn AND a.cifno = b.cfcifn AND a.status <> '2') mst";

			pst1 = connToSIBS.prepareStatement(strSQL);
			ResultSet rs = pst1.executeQuery();
			int i = 0;
			while (rs != null && rs.next()) {
				AccountInfo temAccountInfo = new AccountInfo();
				temAccountInfo.setBank_no(AppUtils.correctStringValue(rs.getString("bank_no")));
				temAccountInfo.setBranch_no(AppUtils.correctStringValue(rs.getString("branch_no")));
				temAccountInfo.setAccount_no(AppUtils.correctStringValue(rs.getString("acct_no")));
				temAccountInfo.setP_acct_no(AppUtils.correctStringValue(rs.getString("p_acct_no")));
				temAccountInfo.setAcct_type(AppUtils.correctStringValue(rs.getString("acct_type")));
				temAccountInfo.setAcct_name(AppUtils.correctStringValue(rs.getString("acct_name")));
				temAccountInfo.setAcct_ccy(AppUtils.correctStringValue(rs.getString("acct_ccy")));
				temAccountInfo.setCif_no(AppUtils.correctStringValue(rs.getString("cif_no")));
				temAccountInfo.setAcct_status(AppUtils.correctStringValue(rs.getString("acct_status")));
				temAccountInfo.setIssued_date(AppUtils.correctStringValue(rs.getString("issued_date")));
				temAccountInfo.setDate_acct_opened(AppUtils.correctStringValue(rs.getString("date_acct_opened")));
				temAccountInfo.setMaturity_date(AppUtils.correctStringValue(rs.getString("maturity_date")));
				temAccountInfo.setEstablish_date(AppUtils.correctStringValue(rs.getString("establish_date")));
				temAccountInfo.setFull_release_date(AppUtils.correctStringValue(rs.getString("full_release_date")));
				temAccountInfo.setProduct_code(AppUtils.correctStringValue(rs.getString("product_code")));
				temAccountInfo.setTerm(AppUtils.correctStringValue(rs.getString("term")));
				temAccountInfo.setAccured_interest_due(AppUtils.correctStringValue(rs.getString("accured_interest_due")));
				processBalance(temAccountInfo, temAccountInfo.getAcct_type(), rs.getString("real_bal"));
				temAccountInfo.setPassbook_no(AppUtils.correctStringValue(rs.getString("passbook_no")));
				temAccountInfo.setInterest_rate(AppUtils.correctStringValue(rs.getString("interest_rate")));
				temAccountInfo.setAccured_interest(AppUtils.correctStringValue(rs.getString("accured_interest")));
				temAccountInfo.setBranch_no(AppUtils.correctStringValue(rs.getString("loan_no")));
				arrAccountInfo.add(temAccountInfo);
				// i++;
			}
		} catch (SQLException e) {
			if (connToSIBS != null) {
				try {
					connToSIBS.rollback();
				} catch (SQLException excep) {
					excep.printStackTrace();
				}
			}
		} finally {
			try {
				if (pst1 != null) {
					pst1.close();
				}
				connToSIBS.setAutoCommit(true);
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return arrAccountInfo;
	}

	private void processBalance(AccountInfo tempAccount, String account_type, String real_bal) {
		if (account_type.equals(Global.COREBBANK_CA_ACCOUNT_TYPE) || account_type.equals(Global.COREBBANK_SA_ACCOUNT_TYPE)) {
			String[] arrAccount = real_bal.split(":");
			tempAccount.setHold_amt(arrAccount[0]);// hold
			tempAccount.setLedger_balance(arrAccount[1]);// ledger
			tempAccount.setAvailable_balance(arrAccount[2]);// avail
			tempAccount.setOverdraft_limit(arrAccount[3]);// overdraft_limit
			tempAccount.setInterest_rate(arrAccount[4]);// interest_rate
			tempAccount.setEarmarked_amt(arrAccount[0]);// earm =hold
		} else if (account_type.equals(Global.COREBBANK_FD_RECEIPT_ACCOUNT_TYPE)) {
			String[] arrAccount = real_bal.split(":");
			tempAccount.setOri_balance(arrAccount[0]);// ori_balance
			tempAccount.setPrincipal_balance(arrAccount[1]);// principal_balance
			tempAccount.setAccured_interest(arrAccount[2]);// accured_interest
			tempAccount.setPenalty_amt(arrAccount[3]);// penalty_amt
			tempAccount.setHold_amt(arrAccount[4]);// hold_amt
			tempAccount.setEarmarked_amt(arrAccount[4]);// hold_amt
			tempAccount.setCurrent_cash_value(arrAccount[5]);// current_cash_value
		} else if (account_type.equals(Global.COREBBANK_FD_GROUP_ACCOUNT_TYPE)) {
			String[] arrAccount = real_bal.split(":");
			tempAccount.setPenalty_amt(arrAccount[4]);// penalty_amt
			tempAccount.setHold_amt(arrAccount[5]);// hold_amt
			tempAccount.setEarmarked_amt(arrAccount[5]);// hold_amt
		} else if (account_type.equals(Global.COREBBANK_LN_ACCOUNT_TYPE)) {
			String[] arrAccount = real_bal.split(":");
			tempAccount.setOri_balance(arrAccount[0]);// original_balance
			tempAccount.setOs_principal(arrAccount[1]);// os_principal
			// tempAccount.seta(arrAccount[2]);// available_limit
			tempAccount.setOs_balance(arrAccount[3]);// os_balance
			tempAccount.setBilled_total_amt(arrAccount[4]);// billed_total_amount
			tempAccount.setBilled_principal(arrAccount[5]);// billed_principal
			tempAccount.setBilled_interest(arrAccount[6]);// billed_interest
			tempAccount.setBilled_late_charge(arrAccount[7]);// billed_late_charge
			tempAccount.setBilled_other_charge(arrAccount[8]);// other_charges
			tempAccount.setOverdraft_limit(arrAccount[9]);// overdraft_limit
			tempAccount.setHold_amt(arrAccount[10]);// hold_amount
			tempAccount.setAccured_interest(arrAccount[11]);// accured_interest
			// tempAccount.seta(arrAccount[12]);// accrued_common_fee
			// tempAccount.seta(arrAccount[13]);// accrued_late_charge
			// tempAccount.seta(arrAccount[14]);// available_limit
			tempAccount.setEarmarked_amt(arrAccount[15]);// earmarked_amt
			tempAccount.setPayment_amt(arrAccount[16]);// payment_amount
			tempAccount.setFinal_payment_amt(arrAccount[17]);// final_payment_amt
		} else {
		}
	}

	public static void main(String agrs[]) {
		AccountInfoDAOImpl imp = new AccountInfoDAOImpl();
		AccountInfo arrAccountInfo = imp.getAccountInfoByAccountNo("11001010024923", "D");
		System.out.println("----------");
	}
}
